﻿Imports System.Data.SqlClient
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.SqlClient.SqlConnection
Imports System.Windows.Forms

Public Class FmTimPhieuNhap

    Private daCtiet As SqlDataAdapter
    Private cmCtiet As SqlCommandBuilder
    Private dsCtiet As DataSet


    Public Sub FillDataGridView11()
        XdKetNoi()
        Dim sql4 As String


        If (CbTimTheoPN.Text <> "") Then
            Try
                sql4 = "select d.SOPN,d.NGAYNHAP,e.TENCC,e.DIACHI,e.SDTHOAI,e.TINH,e.MSTHUE,e.STKHOAN,a.MAHANG, b.TENHANG,a.SOLUONG,a.DONGIA,(a.SOLUONG * a.DONGIA ) as THANHTIEN ,d.THUENHAP,d.TTIEN from (((CHITIETPHIEUNHAP a inner join HANGHOA b on a.MAHANG=b.MAHANG) inner join PHIEUNHAP d on a.SOPN=d.SOPN)inner join NHA_CC e on d.MACC=e.MACC )WHERE d.SOPN='" & CbTimTheoPN.Text & "'"


                Dim daCtiet As New SqlDataAdapter(sql4, KetNoi)
                'khoi tao dataset
                Dim dsCtiet As New DataSet
                ''dien du lieu vao dataset
                daCtiet.Fill(dsCtiet)




                Dim i As Integer
                i = dsCtiet.Tables(0).Rows.Count

                If i <> 0 Then
                    Me.DataGridView1.DataSource = dsCtiet.Tables(0)
                Else
                    MessageBox.Show("Không tìm thấy")
                End If

                KetNoi.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Else
            MsgBox("Bạn vui lòng chọn số phiếu nhập")
        End If


    End Sub



    Public Sub FillDataGridView21()
        XdKetNoi()
        Dim sql4 As String


        If (CbTimNCC.Text <> "") Then
            Try
                sql4 = "select d.SOPN,d.NGAYNHAP,e.TENCC,e.DIACHI,e.SDTHOAI,e.TINH,e.MSTHUE,e.STKHOAN,a.MAHANG, b.TENHANG,a.SOLUONG,a.DONGIA,(a.SOLUONG * a.DONGIA ) as THANHTIEN ,d.THUENHAP,d.TTIEN from (((CHITIETPHIEUNHAP a inner join HANGHOA b on a.MAHANG=b.MAHANG) inner join PHIEUNHAP d on a.SOPN=d.SOPN)inner join NHA_CC e on d.MACC=e.MACC )WHERE e.TENCC=N'" & CbTimNCC.Text & "'"


                Dim daCtiet As New SqlDataAdapter(sql4, KetNoi)
                'khoi tao dataset
                Dim dsCtiet As New DataSet
                ''dien du lieu vao dataset
                daCtiet.Fill(dsCtiet)




                Dim i As Integer
                i = dsCtiet.Tables(0).Rows.Count

                If i <> 0 Then
                    Me.DataGridView1.DataSource = dsCtiet.Tables(0)
                Else
                    MessageBox.Show("Không tìm thấy")
                End If

                KetNoi.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Else
            MsgBox("Bạn vui lòng chọn tên nhà cung cấp")
        End If


    End Sub



    Public Sub FillDataGridView31()
        XdKetNoi()
        Dim sql4 As String


        If (CbTimNgayNH.Text <> "") Then
            Try
                sql4 = "select d.SOPN,d.NGAYNHAP,e.TENCC,e.DIACHI,e.SDTHOAI,e.TINH,e.MSTHUE,e.STKHOAN,a.MAHANG, b.TENHANG,a.SOLUONG,a.DONGIA,(a.SOLUONG * a.DONGIA ) as THANHTIEN ,d.THUENHAP,d.TTIEN from (((CHITIETPHIEUNHAP a inner join HANGHOA b on a.MAHANG=b.MAHANG) inner join PHIEUNHAP d on a.SOPN=d.SOPN)inner join NHA_CC e on d.MACC=e.MACC )WHERE d.NGAYNHAP='" & CbTimNgayNH.Text & "'"


                Dim daCtiet As New SqlDataAdapter(sql4, KetNoi)
                'khoi tao dataset
                Dim dsCtiet As New DataSet
                ''dien du lieu vao dataset
                daCtiet.Fill(dsCtiet)




                Dim i As Integer
                i = dsCtiet.Tables(0).Rows.Count

                If i <> 0 Then
                    Me.DataGridView1.DataSource = dsCtiet.Tables(0)
                Else
                    MessageBox.Show("Không tìm thấy")
                End If

                KetNoi.Close()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Else
            MsgBox("Bạn vui lòng chọn ngày nhập")
        End If


    End Sub

    Public Sub getcb3()
        Dim sql4 As String
        sql4 = "select SOPN from PHIEUNHAP "
        XdKetNoi()
        Dim dancc As New SqlDataAdapter(sql4, KetNoi)
        Dim dsncc As New DataSet
        dancc.Fill(dsncc, "PHIEUNHAP")
        Dim datable As New DataTable()
        datable.Columns.Add("SOPN", GetType(System.String))
        Dim droldRow As DataRow
        Dim drnewRow As DataRow
        For Each droldRow In dsncc.Tables("PHIEUNHAP").Rows()
            drnewRow = datable.NewRow()
            drnewRow("SOPN") = droldRow("SOPN")
            datable.Rows.Add(drnewRow)
        Next
        CbTimTheoPN.DropDownStyle = ComboBoxStyle.DropDownList
        With CbTimTheoPN
            .DataSource = datable
            .DisplayMember = "Description"
            .ValueMember = "SOPN"


        End With

        KetNoi.Close()


    End Sub

    Public Sub getcb4()
        Dim sql4 As String
        sql4 = "select TENCC from NHA_CC "
        XdKetNoi()
        Dim dancc As New SqlDataAdapter(sql4, KetNoi)
        Dim dsncc As New DataSet
        dancc.Fill(dsncc, "NHA_CC")
        Dim datable As New DataTable()
        datable.Columns.Add("TENCC", GetType(System.String))
        Dim droldRow As DataRow
        Dim drnewRow As DataRow
        For Each droldRow In dsncc.Tables("NHA_CC").Rows()
            drnewRow = datable.NewRow()
            drnewRow("TENCC") = droldRow("TENCC")
            datable.Rows.Add(drnewRow)
        Next
        CbTimNCC.DropDownStyle = ComboBoxStyle.DropDownList
        With CbTimNCC
            .DataSource = datable
            .DisplayMember = "Description"
            .ValueMember = "TENCC"


        End With
        KetNoi.Close()



    End Sub


    Public Sub getcb5()
        Dim sql4 As String
        sql4 = "select NGAYNHAP from PHIEUNHAP "
        XdKetNoi()
        Dim dancc As New SqlDataAdapter(sql4, KetNoi)
        Dim dsncc As New DataSet
        dancc.Fill(dsncc, "PHIEUNHAP")
        Dim datable As New DataTable()
        datable.Columns.Add("NGAYNHAP", GetType(System.String))
        Dim droldRow As DataRow
        Dim drnewRow As DataRow
        For Each droldRow In dsncc.Tables("PHIEUNHAP").Rows()
            drnewRow = datable.NewRow()
            drnewRow("NGAYNHAP") = droldRow("NGAYNHAP")
            datable.Rows.Add(drnewRow)
        Next
        CbTimNgayNH.DropDownStyle = ComboBoxStyle.DropDownList
        With CbTimNgayNH
            .DataSource = datable
            .DisplayMember = "NGAYNHAP"
            .ValueMember = "NGAYNHAP"


        End With

        KetNoi.Close()

      


    End Sub



    Private Sub BtThoat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtThoat.Click
        Me.Close()
    End Sub

    Private Sub FmTimPhieuNhap_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        XdKetNoi()

        Dim dieuphoi As New SqlDataAdapter("Select * from PHIEUNHAP", KetNoi)

        Dim DTKH As New DataTable
        dieuphoi.Fill(DTKH)
        KetNoi.Close()
        DataGridView1.DataSource = DTKH
        BindingSource1.DataSource = DTKH
        DataGridView1.DataSource = BindingSource1
        getcb3()
        getcb4()
        getcb5()

        KetNoi.Close()
    End Sub

    Private Sub BtTimPN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtTimPN.Click
        FillDataGridView11()
    End Sub

    Private Sub BtTimNCC_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtTimNCC.Click
        FillDataGridView21()
    End Sub

    Private Sub BtTimNgayNhap_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtTimNgayNhap.Click
        FillDataGridView31()
    End Sub
End Class